-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 29, 2025 at 09:46 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `mydatabase`
--

-- --------------------------------------------------------

--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `department`
--

CREATE TABLE `department` (
  `id` int(11) NOT NULL,
  `department` varchar(200) NOT NULL,
  `college_office` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `department`
--

INSERT INTO `department` (`id`, `department`, `college_office`) VALUES
(1, 'CAS', 'College of Arts and Sciences'),
(2, 'CA', 'College of Agriculture');

-- --------------------------------------------------------

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
  `id` int(11) NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `doc_type` varchar(100) DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `documents`
--

INSERT INTO `documents` (`id`, `employee_id`, `doc_type`, `file_path`, `remarks`, `uploaded_at`, `created_at`, `updated_at`) VALUES
(21, 8, 'Medical Certificate', 'uploads/1761745925_0_ACTIVITY #1.docx', '', '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 14:02:52'),
(22, 8, 'Medical Certificate', 'uploads/1761745925_1_JESSA MAE-MICRO-TEACHING-LP.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(23, 8, 'NBI/Police Clearance', 'uploads/1761745925_0_HYDROCHECK_FINAL1.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(24, 8, 'NBI/Police Clearance', 'uploads/1761745925_1_hydrocheck.pdf', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(25, 8, 'PRC License/Rating', 'uploads/1761745925_0_Alahid_Angeline.pdf', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(26, 8, 'PRC License/Rating', 'uploads/1761745925_1_JESSA MEDICAL.pdf', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(27, 8, 'Transcript of Records', 'uploads/1761745925_0_CHAPTER 1_ INTR-WPS Office.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(28, 8, 'Transcript of Records', 'uploads/1761745925_1_RESEARCH PROPOSAL.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(29, 8, 'PSA Birth Certificate', 'uploads/1761745925_0_Republic_of_the-WPS_Office[2] (AutoRecovered).docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(30, 8, 'PSA Birth Certificate', 'uploads/1761745925_1_Title-Proposal-Template.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(31, 8, 'Certificate of Employment', 'uploads/1761745925_0_THE IMPACT OF CYBERBULLYING ON THE MENTAL HEALTH OF BSCS STUDENT-1.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(32, 8, 'Certificate of Employment', 'uploads/1761745925_1_Cyberbullying and its effects on adolescent psychosocial well.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(33, 8, 'Publication Records', 'uploads/1761745925_0_chapter 1hr.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05'),
(34, 8, 'Publication Records', 'uploads/1761745925_1_HYDROCHECK_FINAL.docx', NULL, '2025-10-29 13:52:05', '2025-10-29 13:52:05', '2025-10-29 13:52:05');

-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `full_name` varchar(150) DEFAULT NULL,
  `college_office` varchar(100) DEFAULT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `employment_type` enum('JO','COS') DEFAULT NULL,
  `contract_status` enum('Active','Expired','Terminated') DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `full_name`, `college_office`, `job_title`, `employment_type`, `contract_status`, `start_date`, `end_date`, `created_at`) VALUES
(8, 'James Cabug', 'College of Arts and Sciences', 'Instructor 2', 'COS', 'Active', '2025-10-30', '2027-10-12', '2025-10-29 13:52:05');

-- --------------------------------------------------------

--
-- Table structure for table `job`
--

CREATE TABLE `job` (
  `id` int(11) NOT NULL,
  `jobname` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `job`
--

INSERT INTO `job` (`id`, `jobname`) VALUES
(1, 'Instructor I'),
(2, 'Instructor II'),
(3, 'Instructor III');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role` enum('public','staff','super') NOT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `name`, `email`, `username`, `password`, `role`, `status`, `created_at`) VALUES
(11, 'System Admin', 'admin', 'admin', '$2y$10$La11LxqHrOZ52P9fArzUqOAQ.bTFk6p.520yPBhe/VCu18qjrEwYe', 'public', 'active', '2025-09-07 01:17:34'),
(12, 'Staff', 'staff', 'staff', '$2y$10$La11LxqHrOZ52P9fArzUqOAQ.bTFk6p.520yPBhe/VCu18qjrEwYe', 'staff', 'active', '2025-10-29 13:27:06'),
(13, 'Super Admin', 'super', 'super', '$2y$10$La11LxqHrOZ52P9fArzUqOAQ.bTFk6p.520yPBhe/VCu18qjrEwYe', 'super', 'active', '2025-10-29 13:27:11');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_audit_user` (`user_id`);

--
-- Indexes for table `department`
--
ALTER TABLE `department`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `documents`
--
ALTER TABLE `documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `job`
--
ALTER TABLE `job`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `audit_logs`
--
ALTER TABLE `audit_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `department`
--
ALTER TABLE `department`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `documents`
--
ALTER TABLE `documents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `job`
--
ALTER TABLE `job`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `documents`
--
ALTER TABLE `documents`
  ADD CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
